﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OracleClient;
using System.Data;

namespace XWG.JWBST.JWBSTDAL
{
    public class TbBstAppealVistDAL
    {

        /// <summary>
        /// 根据时间点获取手机各个模块使用量
        /// </summary>
        /// <param name="datetime"></param>
        /// <returns></returns>
        public static DataTable GetAppealNumByDD(string datetime)
        {
            //string sql = "select count(1) SL,t.modulename,D.DESCRIPTION from TB_BST_VISITS t  inner join tb_bst_dic d on t.modulename=d.code where parentnode='phoneMoudle' and to_char(t.time,'yyyy-mm-dd') = :time group by t.modulename,D.DESCRIPTION";

            string sql = "select count(1) SL,D.DESCRIPTION from TB_BST_APPEAL t inner join tb_bst_dic d on t.source=d.code where parentnode='source' and to_char(t.CREATEDATE,'yyyy-mm-dd') = :time group by t.source,D.DESCRIPTION union all select count(1) sl,t.appealtype DESCRIPTION from V_TB_BST_APPEALMANAGEMENT t where to_char(t.CREATEDATE,'yyyy-mm-dd') = :time group by t.appealtype";
            OracleParameter[] sp = { 
                                    new OracleParameter(":time",datetime)
                                   };
            return OrclHelper.GetDT(sql, sp);
        }

        /// <summary>
        /// 根据时间段获取手机各个模块使用量
        /// </summary>
        /// <param name="starttime"></param>
        /// <param name="endtime"></param>
        /// <returns></returns>
        public static DataTable GetAppealNumByDate(string starttime, string endtime)
        {
            //string sql = " select count(1) SL,t.modulename,D.DESCRIPTION from TB_BST_VISITS t  inner join tb_bst_dic d on t.modulename=d.code where parentnode='phoneMoudle' and to_char(time,'yyyy-mm-dd') between :starttime and :endtime group by t.modulename,D.DESCRIPTION ";
            string sql = "select count(1) SL,D.DESCRIPTION from TB_BST_APPEAL t inner join tb_bst_dic d on t.source=d.code where parentnode='source' and to_char(t.CREATEDATE,'yyyy-mm-dd') between :starttime and :endtime group by t.source,D.DESCRIPTION union all select count(1) sl,t.appealtype DESCRIPTION from V_TB_BST_APPEALMANAGEMENT t where to_char(CREATEDATE,'yyyy-mm-dd') between :starttime and :endtime group by t.appealtype";
            OracleParameter[] sp = { 
                                   new OracleParameter(":starttime",starttime),
                                   new OracleParameter(":endtime",endtime)
                                   };
            return OrclHelper.GetDT(sql, sp);
        }


        /// <summary>
        /// 按照月份获取手机各个模块使用量
        /// </summary>
        /// <param name="dateMM"></param>
        /// <returns></returns>
        public static DataTable GetAppealNumByMM(string dateMM)
        {
            //string sql = "select count(1) SL,t.modulename,D.DESCRIPTION from TB_BST_VISITS t inner join tb_bst_dic d on t.modulename=d.code where parentnode='phoneMoudle' and to_char(t.time,'yyyy-mm') = :time group by t.modulename,D.DESCRIPTION";
            string sql = "select count(1) SL,D.DESCRIPTION from TB_BST_APPEAL t inner join tb_bst_dic d on t.source=d.code where parentnode='source' and to_char(t.CREATEDATE,'yyyy-mm') = :time group by t.source,D.DESCRIPTION union all select count(1) sl,t.appealtype DESCRIPTION from V_TB_BST_APPEALMANAGEMENT t where to_char(t.CREATEDATE,'yyyy-mm') = :time group by t.appealtype";
            OracleParameter[] sp = { 
                                   new OracleParameter(":time",dateMM)
                                    };
            return OrclHelper.GetDT(sql, sp);
        }
    }
}
